package com.qh.resourcehub.usersphere.dao.impl;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.qh.resourcehub.usersphere.dao.SyncUnifiedUserDao;
import com.qh.resourcehub.usersphere.entity.SyncUnifiedUserRequest;
import com.qh.resourcehub.utils.XuguUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Repository("syncUnifiedUserDao")
public class SyncUnifiedUserDaoImpl implements SyncUnifiedUserDao {
    @Autowired
    private XuguUtils xuguUtils;

    /**
     * 新增用户及岗位
     * @param syncUnifiedUserRequest
     * @return
     */
    @Override
    public int addUser(SyncUnifiedUserRequest syncUnifiedUserRequest) {
        int result = 0;
        Connection connection = null;
        PreparedStatement pstmtUser = null;
        PreparedStatement pstmtEmp = null;
        PreparedStatement pstmtCheck = null;
        ResultSet rs = null;

        try {
            connection = xuguUtils.getConnection();
            connection.setAutoCommit(false);

            LinkedHashMap userdata = (LinkedHashMap) syncUnifiedUserRequest.getData();
            String userCode = (String) userdata.get("userCode");

            // 先判断 userCode 是否存在
            String checkSql = "SELECT COUNT(*) FROM USER_DATA WHERE USERCODE = ?";
            pstmtCheck = connection.prepareStatement(checkSql);
            pstmtCheck.setString(1, userCode);
            rs = pstmtCheck.executeQuery();

            if (rs.next() && rs.getInt(1) > 0) {
                // 用户已存在，直接返回
                return 2;
            }

            List<Map<String, Object>> deptCode = (List<Map<String, Object>>) userdata.get("deptCode");
            ObjectMapper objectMapper = new ObjectMapper();
            String deptCodeJson = objectMapper.writeValueAsString(deptCode);

            // 插入用户表
            String sqlUser = "INSERT INTO USER_DATA (NAME, USERCODE, LOGINNAME, PHONE, SEX, EMAIL, ACCOUNTTYPE, STATUS, G_CODE, CERTCODE, JOBSTATUS, IDCARD, PHOTO, DEPTCODE)" +
                    " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmtUser = connection.prepareStatement(sqlUser);
            pstmtUser.setString(1, (String) userdata.get("name"));
            pstmtUser.setString(2, userCode);
            pstmtUser.setString(3, (String) userdata.get("loginName"));
            pstmtUser.setString(4, (String) userdata.get("phone"));
            pstmtUser.setString(5, (String) userdata.get("sex"));
            pstmtUser.setString(6, (String) userdata.get("email"));
            pstmtUser.setString(7, (String) userdata.get("accountType"));
            pstmtUser.setString(8, (String) userdata.get("status"));
            pstmtUser.setString(9, (String) userdata.get("G_Code"));
            pstmtUser.setString(10, (String) userdata.get("certCode"));
            pstmtUser.setString(11, (String) userdata.get("jobStatus"));
            pstmtUser.setString(12, (String) userdata.get("IdCard"));
            pstmtUser.setString(13, (String) userdata.get("photo"));
            pstmtUser.setString(14, deptCodeJson);
            result = pstmtUser.executeUpdate();

            // 插入岗位表
            String sqlEmp = "INSERT INTO EMPLOYEEDEPARTMENTINFO (DEPTUNICODE, CODE, MAINFLAG, ORGCODE, ORDERS, POSITIONLEVEL, OFFICEPHONE, POSITION, DUTY, ISSPECIFICALLYCITY, ISLEADER, OPENTYPE, JOBSTATUS, OFFICEROLE, USERCODE)" +
                    " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmtEmp = connection.prepareStatement(sqlEmp);
            for (Map<String, Object> dept : deptCode) {
                pstmtEmp.setString(1, (String) dept.get("deptUnicode"));
                pstmtEmp.setString(2, (String) dept.get("code"));
                pstmtEmp.setString(3, (String) dept.get("mainFlag"));
                pstmtEmp.setString(4, (String) dept.get("orgCode"));
                pstmtEmp.setString(5, (String) dept.get("orders"));
                pstmtEmp.setString(6, (String) dept.get("positionLevel"));
                pstmtEmp.setString(7, (String) dept.get("officePhone"));
                pstmtEmp.setString(8, (String) dept.get("position"));
                pstmtEmp.setString(9, (String) dept.get("duty"));
                pstmtEmp.setString(10, (String) dept.get("isSpecificallyCity"));
                pstmtEmp.setString(11, (String) dept.get("isLeader"));
                pstmtEmp.setString(12, (String) dept.get("openType"));
                pstmtEmp.setString(13, (String) dept.get("jobStatus"));
                pstmtEmp.setString(14, (String) dept.get("officeRole"));
                pstmtEmp.setString(15, userCode);
                pstmtEmp.executeUpdate();
            }

            connection.commit();
            return result;
        } catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException rollbackEx) {
                    rollbackEx.printStackTrace();
                }
            }
            e.printStackTrace();
            return result;
        } finally {
            try {
                if (rs != null){
                    rs.close();
                }
                if (pstmtCheck != null){
                    pstmtCheck.close();
                }
                if (pstmtUser != null){
                    pstmtUser.close();
                }
                if (pstmtEmp != null){
                    pstmtEmp.close();
                }
                if (connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 修改用户信息以及岗位表
     * @param syncUnifiedUserRequest
     * @return
     */
    @Override
    public int saveUser(SyncUnifiedUserRequest syncUnifiedUserRequest) {
        int result = 0;
        Connection connection = null;
        PreparedStatement pstmtCheck = null;
        PreparedStatement pstmtUser = null;
        PreparedStatement pstmtEmpDelete = null;
        PreparedStatement pstmtEmpInsert = null;

        try {
            connection = xuguUtils.getConnection();
            connection.setAutoCommit(false);
            LinkedHashMap userdata = (LinkedHashMap) syncUnifiedUserRequest.getData();
            List<Map<String, Object>> deptCode = (List<Map<String, Object>>) userdata.get("deptCode");

            String userCode = (String) userdata.get("userCode");

            // 1. 查询是否已存在该用户
            String checkSql = "SELECT COUNT(*) FROM USER_DATA WHERE USERCODE = ?";
            pstmtCheck = connection.prepareStatement(checkSql);
            pstmtCheck.setString(1, userCode);
            ResultSet rs = pstmtCheck.executeQuery();
            boolean userExists = false;
            if (rs.next() && rs.getInt(1) > 0) {
                userExists = true;
            }

            if (userExists) {
                // 2.1 存在则更新 USER_DATA
                String updateSql = "UPDATE USER_DATA SET NAME=?, LOGINNAME=?, PHONE=?, SEX=?, EMAIL=?, ACCOUNTTYPE=?, STATUS=?, G_CODE=?, CERTCODE=?, JOBSTATUS=?, IDCARD=?, PHOTO=? WHERE USERCODE=?";
                pstmtUser = connection.prepareStatement(updateSql);
                pstmtUser.setString(1, (String) userdata.get("name"));
                pstmtUser.setString(2, (String) userdata.get("loginName"));
                pstmtUser.setString(3, (String) userdata.get("phone"));
                pstmtUser.setString(4, (String) userdata.get("sex"));
                pstmtUser.setString(5, (String) userdata.get("email"));
                pstmtUser.setString(6, (String) userdata.get("accountType"));
                pstmtUser.setString(7, (String) userdata.get("status"));
                pstmtUser.setString(8, (String) userdata.get("G_Code"));
                pstmtUser.setString(9, (String) userdata.get("certCode"));
                pstmtUser.setString(10, (String) userdata.get("jobStatus"));
                pstmtUser.setString(11, (String) userdata.get("idCard"));
                pstmtUser.setString(12, (String) userdata.get("photo"));
                pstmtUser.setString(13, userCode);
                result = pstmtUser.executeUpdate();

                // 2.2 删除原岗位表记录
                String deleteEmpSql = "DELETE FROM EMPLOYEEDEPARTMENTINFO WHERE USERCODE = ?";
                pstmtEmpDelete = connection.prepareStatement(deleteEmpSql);
                pstmtEmpDelete.setString(1, userCode);
                pstmtEmpDelete.executeUpdate();
            } else {
                // 3.1 不存在则插入用户数据
                String insertUserSql = "INSERT INTO USER_DATA (NAME, USERCODE, LOGINNAME, PHONE, SEX, EMAIL, ACCOUNTTYPE, STATUS, G_CODE, CERTCODE, JOBSTATUS, IDCARD, PHOTO, DEPTCODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                pstmtUser = connection.prepareStatement(insertUserSql);

                ObjectMapper objectMapper = new ObjectMapper();
                String deptCodeJson = objectMapper.writeValueAsString(deptCode);

                pstmtUser.setString(1, (String) userdata.get("name"));
                pstmtUser.setString(2, userCode);
                pstmtUser.setString(3, (String) userdata.get("loginName"));
                pstmtUser.setString(4, (String) userdata.get("phone"));
                pstmtUser.setString(5, (String) userdata.get("sex"));
                pstmtUser.setString(6, (String) userdata.get("email"));
                pstmtUser.setString(7, (String) userdata.get("accountType"));
                pstmtUser.setString(8, (String) userdata.get("status"));
                pstmtUser.setString(9, (String) userdata.get("G_Code"));
                pstmtUser.setString(10, (String) userdata.get("certCode"));
                pstmtUser.setString(11, (String) userdata.get("jobStatus"));
                pstmtUser.setString(12, (String) userdata.get("idCard"));
                pstmtUser.setString(13, (String) userdata.get("photo"));
                pstmtUser.setString(14, deptCodeJson);

                result = pstmtUser.executeUpdate();
            }

            // 4. 插入岗位表
            String insertEmpSql = "INSERT INTO EMPLOYEEDEPARTMENTINFO (DEPTUNICODE, CODE, MAINFLAG, ORGCODE, ORDERS, POSITIONLEVEL, OFFICEPHONE, POSITION, DUTY, ISSPECIFICALLYCITY, ISLEADER, OPENTYPE, JOBSTATUS, OFFICEROLE, USERCODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmtEmpInsert = connection.prepareStatement(insertEmpSql);
            for (Map<String, Object> dept : deptCode) {
                pstmtEmpInsert.setString(1, (String) dept.get("deptUnicode"));
                pstmtEmpInsert.setString(2, (String) dept.get("code"));
                pstmtEmpInsert.setString(3, (String) dept.get("mainFlag"));
                pstmtEmpInsert.setString(4, (String) dept.get("orgCode"));
                pstmtEmpInsert.setString(5, (String) dept.get("orders"));
                pstmtEmpInsert.setString(6, (String) dept.get("positionLevel"));
                pstmtEmpInsert.setString(7, (String) dept.get("officePhone"));
                pstmtEmpInsert.setString(8, (String) dept.get("position"));
                pstmtEmpInsert.setString(9, (String) dept.get("duty"));
                pstmtEmpInsert.setString(10, (String) dept.get("isSpecificallyCity"));
                pstmtEmpInsert.setString(11, (String) dept.get("isLeader"));
                pstmtEmpInsert.setString(12, (String) dept.get("openType"));
                pstmtEmpInsert.setString(13, (String) dept.get("jobStatus"));
                pstmtEmpInsert.setString(14, (String) dept.get("officeRole"));
                pstmtEmpInsert.setString(15, userCode);

                result = pstmtEmpInsert.executeUpdate();
            }

            connection.commit();
            return result;

        } catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException rollbackEx) {
                    rollbackEx.printStackTrace();
                }
            }
            e.printStackTrace();
            return result;
        } finally {
            try {
                if (pstmtCheck != null){
                    pstmtCheck.close();
                }
                if (pstmtUser != null) {
                    pstmtUser.close();
                }
                if (pstmtEmpDelete != null){
                    pstmtEmpDelete.close();
                }
                if (pstmtEmpInsert != null){
                    pstmtEmpInsert.close();
                }
                if (connection != null){
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 删除用户和岗位
     * @param syncUnifiedUserRequest
     * @return
     */
    @Override
    public int delUser(SyncUnifiedUserRequest syncUnifiedUserRequest) {
        int result = 0;
        Connection connection = null;
        PreparedStatement pstmtUser = null;
        PreparedStatement pstmtEmp = null;

        try {
            connection = xuguUtils.getConnection();
            connection.setAutoCommit(false);
            LinkedHashMap userdata = (LinkedHashMap) syncUnifiedUserRequest.getData();
            // 删除用户
            String sqlUser = "DELETE FROM USER_DATA WHERE USERCODE=?";
            pstmtUser = connection.prepareStatement(sqlUser);
            pstmtUser.setString(1, (String) userdata.get("userCode"));

            result = pstmtUser.executeUpdate();

            // 删除岗位表中的相关信息
            String sqlEmp = "DELETE FROM EMPLOYEEDEPARTMENTINFO WHERE USERCODE=?";
            pstmtEmp = connection.prepareStatement(sqlEmp);
            pstmtEmp.setString(1, (String) userdata.get("userCode"));
            result = pstmtEmp.executeUpdate();
            connection.commit();
            return result; // 表示成功
        } catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback(); // 🛑 出错回滚
                } catch (SQLException rollbackEx) {
                    rollbackEx.printStackTrace();
                }
            }
            e.printStackTrace();
            return result;
        } finally {
            try {
                if (pstmtUser != null) {
                    pstmtUser.close();
                }
                if (pstmtEmp != null) {
                    pstmtEmp.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    @Override
    public int addDeprt(SyncUnifiedUserRequest syncUnifiedUserRequest) {
        int result=0;
        Connection connection = xuguUtils.getConnection();
        try {
            LinkedHashMap deprtdata = (LinkedHashMap) syncUnifiedUserRequest.getData();
            String sqlInsertDept = "INSERT INTO DEPARTMENT_DATA (" +
                    "DEPTUNICODE, DEPTCODE, DEPTNAME, FULLNAME, DEPTTYPE, EXISTTYPE, " +
                    "ISSPECIFICALLYCITY, DEPTDSCR, ORGCODE, PARENTCODE, STATUS, ORDERS, " +
                    "DEPTLEVEL, PERSONCHARGE, DUTY, PHONE, DEPTNATURE, ISCOMPLEX, ISLEGALPERSON) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            try (PreparedStatement pstmt = connection.prepareStatement(sqlInsertDept)) {
                pstmt.setString(1, (String) deprtdata.get("deptUnicode"));
                pstmt.setString(2, (String) deprtdata.get("deptCode"));
                pstmt.setString(3, (String) deprtdata.get("deptName"));
                pstmt.setString(4, (String) deprtdata.get("fullName"));
                pstmt.setString(5, (String) deprtdata.get("deptType"));
                pstmt.setString(6, (String) deprtdata.get("existType"));
                pstmt.setString(7, (String) deprtdata.get("isSpecificallyCity"));
                pstmt.setString(8, (String) deprtdata.get("deptDscr"));
                pstmt.setString(9, (String) deprtdata.get("orgCode"));
                pstmt.setString(10, (String) deprtdata.get("parentCode"));
                pstmt.setString(11, (String) deprtdata.get("status"));
                pstmt.setString(12, (String) deprtdata.get("orders"));
                pstmt.setString(13, (String) deprtdata.get("deptLevel"));
                pstmt.setString(14, (String) deprtdata.get("personCharge"));
                pstmt.setString(15, (String) deprtdata.get("duty"));
                pstmt.setString(16, (String) deprtdata.get("phone"));
                pstmt.setString(17, (String) deprtdata.get("deptNature"));
                pstmt.setString(18, (String) deprtdata.get("isComplex"));
                pstmt.setString(19, (String) deprtdata.get("isLegalPerson"));
                int insert = pstmt.executeUpdate();
                return insert;
            }
        }catch (SQLException e) {
            // 捕获唯一约束违反的异常
            if (e.getMessage().contains("违反唯一值约束")) {
                // 如果是唯一约束违反，返回400或500等合适的响应
                return -1; // 可以返回自定义的错误代码，例如-1代表违反唯一约束
            } else {
                e.printStackTrace();
                return result; // 如果是其他SQL错误，返回0或其他值
            }
        } catch (Exception e) {
            e.printStackTrace();
            return result; // 处理其他异常
        }finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public int saveDeprt(SyncUnifiedUserRequest syncUnifiedUserRequest) {
        int result = 0;
        Connection connection = xuguUtils.getConnection();
        LinkedHashMap deprtdata = (LinkedHashMap) syncUnifiedUserRequest.getData();
        String sqlUpdateDept = "UPDATE DEPARTMENT_DATA SET " +
                "DEPTNAME=?, FULLNAME=?, DEPTTYPE=?, EXISTTYPE=?, " +
                "ISSPECIFICALLYCITY=?, DEPTDSCR=?, ORGCODE=?, PARENTCODE=?, STATUS=?, ORDERS=?, " +
                "DEPTLEVEL=?, PERSONCHARGE=?, DUTY=?, PHONE=?, DEPTNATURE=?, ISCOMPLEX=?, ISLEGALPERSON=? " +
                "WHERE DEPTUNICODE=? and DEPTCODE=?";
        try (PreparedStatement pstmt = connection.prepareStatement(sqlUpdateDept)) {

            pstmt.setString(1, (String) deprtdata.get("deptName"));
            pstmt.setString(2, (String) deprtdata.get("fullName"));
            pstmt.setString(3, (String) deprtdata.get("deptType"));
            pstmt.setString(4, (String) deprtdata.get("existType"));
            pstmt.setString(5, (String) deprtdata.get("isSpecificallyCity"));
            pstmt.setString(6, (String) deprtdata.get("deptDscr"));
            pstmt.setString(7, (String) deprtdata.get("orgCode"));
            pstmt.setString(8, (String) deprtdata.get("parentCode"));
            pstmt.setString(9, (String) deprtdata.get("status"));
            pstmt.setString(10, (String) deprtdata.get("orders"));
            pstmt.setString(11, (String) deprtdata.get("deptLevel"));
            pstmt.setString(12, (String) deprtdata.get("personCharge"));
            pstmt.setString(13, (String) deprtdata.get("duty"));
            pstmt.setString(14, (String) deprtdata.get("phone"));
            pstmt.setString(15, (String) deprtdata.get("deptNature"));
            pstmt.setString(16, (String) deprtdata.get("isComplex"));
            pstmt.setString(17, (String) deprtdata.get("isLegalPerson"));
            // Set the WHERE condition
            pstmt.setString(18, (String) deprtdata.get("deptUnicode"));
            pstmt.setString(19, (String) deprtdata.get("deptCode"));
            result = pstmt.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return result;
        }
    }

    @Override
    public int delDeprt(SyncUnifiedUserRequest syncUnifiedUserRequest) {
        int result=0;
        Connection connection = xuguUtils.getConnection();
        String sqlDeleteDept = "DELETE FROM DEPARTMENT_DATA WHERE DEPTUNICODE=? and DEPTCODE=? ";
        LinkedHashMap deprtdata = (LinkedHashMap) syncUnifiedUserRequest.getData();
        try (PreparedStatement pstmt = connection.prepareStatement(sqlDeleteDept)) {
            pstmt.setString(1, (String) deprtdata.get("deptUnicode"));
            pstmt.setString(2, (String) deprtdata.get("deptCode"));
                result = pstmt.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return result;
        }
    }
}
